/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ve.usb.cep.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import ve.usb.cep.util.sqlWhere;
import ve.usb.cep.util.sqlWhereException;

/**
 *
 * @author Andras
 */
public class SelectObject {

    protected Connection conn;
    protected String table = "";
    protected short rows = -1;
    protected short page = 1;
    protected String sidx = "";
    protected String sord = "";
    protected sqlWhere filters = new sqlWhere();
    protected String[] fields;
    protected String[] types;
    protected String joinTable = "";
    protected String localColumn = "";
    protected String remoteColumn = "";

    public SelectObject(){
        
    }

    public SelectObject(short rows, short page, String sidx, String sord, sqlWhere filters){
        this.rows = rows;
        this.page = page;
        this.sidx = sidx;
        this.sord = sord;
        this.filters = filters;
    }

    public short getPage(){
        return page;
    }

    public short getRows(){
        return rows;
    }

    public String getSidx(){
        return sidx;
    }

    public void setSidx(String sidx){
        this.sidx = sidx;
    }

    public void setConnection(Connection conn){
        this.conn = conn;
    }
    public void setTable(String table){
        this.table = table;
    }
    public void setFields(String[] fields){
        this.fields = fields;
    }
    public void setTypes(String[] types){
        this.types = types;
    }
    public void setJoin(String joinTable, String localColumn, String remoteColumn){
        this.joinTable = joinTable;
        this.localColumn = localColumn;
        this.remoteColumn = remoteColumn;
    }
    public PreparedStatement getCountQuery()
        throws SelectException
    {
        return _getQuery(true);
    }
    public PreparedStatement getSearchQuery()
        throws SelectException
    {
        return _getQuery(false);
    }
    public PreparedStatement _getQuery(boolean onlyCount)
        throws SelectException
    {
        String q = "SELECT " + (onlyCount ? "COUNT(*)" : '*') + " FROM " + table;
        if (!"".equals(joinTable)){
            q += ", " + joinTable;
        }
        String groupOp;
        sqlWhere.rule[] rules;
        String[][] vals = null;
        String joiner = "";

        if (null != filters){
            String s;
            try{
                s = filters.toSql(fields, types);
            }catch(sqlWhereException e){
                e.printStackTrace();
                throw new SelectException(e.getMessage());
            }
            if (!"".equals(s)){
                vals = filters.getPreparedValues();
                if (!"".equals(localColumn) ){
                    joiner = " AND ";
                    s = "(" + s + ")";
                }
                q += " WHERE " + s;
            }else{
                joiner = " WHERE ";
            }
        }

        if (!"".equals(localColumn)){
            q += joiner + localColumn + "=" + remoteColumn;
        }

        if (!onlyCount){

            if (!"".equals(sidx)){
                q += " ORDER BY " + sidx + ' ' + sord;
            }

            q += " OFFSET " + Integer.toString(rows * (page - 1));

            q += " LIMIT " + Integer.toString(rows);
        }

        try{
            System.out.println(q);
            PreparedStatement p = conn.prepareStatement(q);
            if (null != vals){
                setValues(p, vals);
            }
            return p;
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new SelectException(e.getMessage());
        }
    }

    protected void setValues(PreparedStatement p, String[][] vals)
        throws SelectException
    {
        int i = 0;
        for (String[] v : vals){
            i ++;
            try{
                if ("short".equals(v[1])){
                    p.setShort(i, (short) Integer.parseInt(v[0]));
                }else if ("int".equals(v[1])){
                    p.setInt(i, Integer.parseInt(v[0]));
                }else{
                    p.setString(i, v[0]);
                }
            }catch(SQLException e){
                e.printStackTrace();
                throw new SelectException(e.getMessage());
            }catch(NumberFormatException e){
                e.printStackTrace();
                throw new SelectException("Se pidió comparar con un número: " + v[0]);
            }
        }
    }


}